﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using bdxyit.com.RecruitmentAppSys.IDAL;
using bdxyit.com.RecruitmentAppSys.Models;
using System.Data.SqlClient;
using System.Data;
using bdxyit.com.RecruitmentAppSys.DBUtility;


namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class OccupationalQualificationInfoDAL : IOccupationalQualificationInfoDAL
    {
        private const string SQL_SELECT_OCCUPATIONALQUALIFICATIONINFO = " select   ID,StartTime,EndTime,OccQuaID,OccQuaName,OccQuaLevelID,OccQuaNum,UserID from OccupationalQualificationInfos ";
        private const string SQL_SELECT_OCCUPATIONALQUALIFICATIONINFO_BY_USERID = " select  top 1 ID,StartTime,EndTime,OccQuaID,OccQuaName,OccQuaLevelID,OccQuaNum,UserID from OccupationalQualificationInfos "
                                                                                 + "  where UserID=@UserID";
        private const string SQL_DELETE_OCCUPATIONALQUALIFICATIONINFO_BY_USERID = " delete from OccupationalQualificationInfos "
                                                                                 + "  where UserID=@UserID";
        private const string SQL_UPDATE_OCCUPATIONALQUALIFICATIONINFO_BY_USERID = " update OccupationalQualificationInfos set "
                                                                                + " ID=@ID,"
                                                                                + " StartTime=@StartTime,"
                                                                                + " EndTime=@EndTime,"
                                                                                + " OccQuaID=@OccQuaID,"
                                                                                + " OccQuaName=@OccQuaName,"
                                                                                + " OccQuaLevelID=@OccQuaLevelID,"
                                                                                + " OccQuaNum=@OccQuaNum,"
                                                                                + " UserID=@UserID"
                                                                                + "  where UserID=@UserID";
        private const string SQL_INSERT_OCCUPATIONALQUALIFICATIONINFO = " insert into OccupationalQualificationInfos("
                                                                        + " ID,StartTime,EndTime,OccQuaID,OccQuaName,OccQuaLevelID,OccQuaNum,UserID)"
                                                                        + "  values ("
                                                                        + " @ID,@StartTime,@EndTime,@OccQuaID,@OccQuaName,@OccQuaLevelID,@OccQuaNum,@UserID)";
        private const string FIELD_ID = "@ID";
        private const string FIELD_StartTime = "@StartTime";
        private const string FIELD_EndTime = "@EndTime";
        private const string FIELD_OccQuaID = "@OccQuaID";
        private const string FIELD_OccQuaName = "@OccQuaName";
        private const string FIELD_OccQuaLevelID = "@OccQuaLevelID";
        private const string FIELD_OccQuaNum = "@OccQuaNum";
        private const string FIELD_UserID = "@UserID";

        public bool InsertOccupationalQualificationInfo(OccupationalQualificationInfo cmodel)
        {
            bool flag = false;
            SqlParameter[] OccupationalQualificationInfoParms = GetOccupationalQualificationInfoParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句
            OccupationalQualificationInfoParms[0].Value = cmodel.ID;
            OccupationalQualificationInfoParms[1].Value = cmodel.StartTime;
            OccupationalQualificationInfoParms[2].Value = cmodel.EndTime;
            OccupationalQualificationInfoParms[3].Value = cmodel.OccQuaID;
            OccupationalQualificationInfoParms[4].Value = cmodel.OccQuaName;
            OccupationalQualificationInfoParms[5].Value = cmodel.OccQuaLevelID;
            OccupationalQualificationInfoParms[6].Value = cmodel.OccQuaNum;
            OccupationalQualificationInfoParms[7].Value = cmodel.UserID;

            foreach (SqlParameter parm in OccupationalQualificationInfoParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_OCCUPATIONALQUALIFICATIONINFO;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public bool DeleteOccupationalQualificationInfo(string UserID)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_UserID, SqlDbType.VarChar, 50);
            parm.Value = UserID;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_OCCUPATIONALQUALIFICATIONINFO_BY_USERID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public bool UpdateOccupationalQualificationInfo(OccupationalQualificationInfo cmodel)
        {
            bool flag = false;
            SqlParameter[] OccupationalQualificationInfoParms = GetOccupationalQualificationInfoParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句
            OccupationalQualificationInfoParms[0].Value = cmodel.ID;
            OccupationalQualificationInfoParms[1].Value = cmodel.StartTime;
            OccupationalQualificationInfoParms[2].Value = cmodel.EndTime;
            OccupationalQualificationInfoParms[3].Value = cmodel.OccQuaID;
            OccupationalQualificationInfoParms[4].Value = cmodel.OccQuaName;
            OccupationalQualificationInfoParms[5].Value = cmodel.OccQuaLevelID;
            OccupationalQualificationInfoParms[6].Value = cmodel.OccQuaNum;
            OccupationalQualificationInfoParms[7].Value = cmodel.UserID;

            foreach (SqlParameter parm in OccupationalQualificationInfoParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_OCCUPATIONALQUALIFICATIONINFO_BY_USERID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public OccupationalQualificationInfo SelectOccupationalQualificationInfoByUserID(string UserID)
        {
            OccupationalQualificationInfo cmodel = null;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_UserID, SqlDbType.VarChar, 50);
            parm.Value = UserID;
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_OCCUPATIONALQUALIFICATIONINFO_BY_USERID, parm))
            {
                if (dr.Read())
                {
                    cmodel = new OccupationalQualificationInfo();
                    if (dr.HasRows == true)
                    {
                        if (dr["ID"] != null && dr["ID"].ToString() != "")
                        {
                            cmodel.ID = int.Parse(dr["ID"].ToString());
                        }
                        if (dr["StartTime"] != null && dr["StartTime"].ToString() != "")
                        {
                            cmodel.StartTime = DateTime.Parse(dr["StartTime"].ToString());
                        }
                        if (dr["EndTime"] != null && dr["EndTime"].ToString() != "")
                        {
                            cmodel.EndTime = DateTime.Parse(dr["EndTime"].ToString());
                        }
                        if (dr["OccQuaID"] != null && dr["OccQuaID"].ToString() != "")
                        {
                            cmodel.OccQuaID = dr["OccQuaID"].ToString();
                        }
                        if (dr["OccQuaName"] != null && dr["OccQuaName"].ToString() != "")
                        {
                            cmodel.OccQuaName = dr["OccQuaName"].ToString();
                        }
                        if (dr["OccQuaLevelID"] != null && dr["OccQuaLevelID"].ToString() != "")
                        {
                            cmodel.OccQuaLevelID = dr["OccQuaLevelID"].ToString();
                        }
                        if (dr["OccQuaNum"] != null && dr["OccQuaNum"].ToString() != "")
                        {
                            cmodel.OccQuaNum = dr["OccQuaNum"].ToString();
                        }
                        if (dr["UserID"] != null && dr["UserID"].ToString() != "")
                        {
                            cmodel.UserID = dr["UserID"].ToString();
                        }
                    }
                }
            }
            return cmodel;
        }
        public List<OccupationalQualificationInfo> SelectOccupationalQualificationInfo()
        {
            List<OccupationalQualificationInfo> selectlist = new List<OccupationalQualificationInfo>();
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_OCCUPATIONALQUALIFICATIONINFO))
            {
                while (dr.Read())
                {
                    OccupationalQualificationInfo cmodel = new OccupationalQualificationInfo();
                    //写添赋值语句
                    if (dr["ID"] != null && dr["ID"].ToString() != "")
                    {
                        cmodel.ID = int.Parse(dr["ID"].ToString());
                    }
                    if (dr["StartTime"] != null && dr["StartTime"].ToString() != "")
                    {
                        cmodel.StartTime = DateTime.Parse(dr["StartTime"].ToString());
                    }
                    if (dr["EndTime"] != null && dr["EndTime"].ToString() != "")
                    {
                        cmodel.EndTime = DateTime.Parse(dr["EndTime"].ToString());
                    }
                    if (dr["OccQuaID"] != null && dr["OccQuaID"].ToString() != "")
                    {
                        cmodel.OccQuaID = dr["OccQuaID"].ToString();
                    }
                    if (dr["OccQuaName"] != null && dr["OccQuaName"].ToString() != "")
                    {
                        cmodel.OccQuaName = dr["OccQuaName"].ToString();
                    }
                    if (dr["OccQuaLevelID"] != null && dr["OccQuaLevelID"].ToString() != "")
                    {
                        cmodel.OccQuaLevelID = dr["OccQuaLevelID"].ToString();
                    }
                    if (dr["OccQuaNum"] != null && dr["OccQuaNum"].ToString() != "")
                    {
                        cmodel.OccQuaNum = dr["OccQuaNum"].ToString();
                    }
                    if (dr["UserID"] != null && dr["UserID"].ToString() != "")
                    {
                        cmodel.UserID = dr["UserID"].ToString();
                    }

                    selectlist.Add(cmodel);
                }
            }
            return selectlist;
        }
        private SqlParameter[] GetOccupationalQualificationInfoParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
                	new SqlParameter(FIELD_ID, SqlDbType.Int,4),
					new SqlParameter(FIELD_StartTime, SqlDbType.DateTime),
					new SqlParameter(FIELD_EndTime, SqlDbType.DateTime),
					new SqlParameter(FIELD_OccQuaID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_OccQuaName, SqlDbType.VarChar,100),
					new SqlParameter(FIELD_OccQuaLevelID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_OccQuaNum, SqlDbType.VarChar,100),
					new SqlParameter(FIELD_UserID, SqlDbType.VarChar,50)};
            return parms;
        }

    }
}